package com.example.itcacp.repository.mybatis;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.itcacp.entity.User;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Repository;

import java.util.List;


/**
 * UserMapper数据访问层 : 用于数据库操作 - 接口类
 * @author Jiuuan
 * */

@Repository  // 修饰数据访问对象层组件 --- 数据库操作
public interface UserMapper extends BaseMapper<User> {

    /**
     *
     * 使用userid查询用户及角色
     */
    @Select("SELECT u.*, r.role_name, r.description FROM it_user u " +
            "LEFT JOIN it_role r ON u.role_id = r.role_id " +
            "WHERE u.user_id = #{user_id}")
    @Results({
            @Result(property = "user_id", column = "user_id"),
            @Result(property = "profile_photo_path", column = "profile_photo_path"),
            @Result(property = "role_id", column = "role_id"),
            @Result(property = "tags", column = "tags"),
            @Result(property = "role.roleId", column = "role_id"),
            @Result(property = "role.roleName", column = "role_name"),
            @Result(property = "role.description", column = "description"),
            @Result(property = "created_at", column = "created_at"),
            @Result(property = "updated_at", column = "updated_at")
    })
    User findById(String user_id);

    @Select ( "SELECT username, profile_photo_path FROM it_user WHERE user_id = #{user_id}" )
    @Results({
            @Result(property = "user_id", column = "user_id"),
            @Result(property = "username", column = "username"),
            @Result(property = "profile_photo_path", column = "profile_photo_path")
    })
    User findBolgUserById (String user_id);
    /**
     * 根据用户名查询用户及其角色信息
     */
    @Select("SELECT u.*, r.role_name, r.description FROM it_user u " +
            "LEFT JOIN it_role r ON u.role_id = r.role_id " +
            "WHERE u.username = #{username}")
    @Results({
            @Result(property = "user_id", column = "user_id"),
            @Result(property = "role_id", column = "role_id"), // 补充 role_id 映射 ,  在响应数据中没有相关数据，故此加上显式映射
            @Result(property = "tags", column = "tags"),
            @Result(property = "created_at", column = "created_at"), // 补充 created_at 映射
            @Result(property = "updated_at", column = "updated_at"),
            @Result(property = "profile_photo_path", column = "profile_photo_path"),
            @Result(property = "role.roleId", column = "role_id"),
            @Result(property = "role.roleName", column = "role_name"),
            @Result(property = "role.description", column = "description")
    })
    User selectUserWithRoleByUsername(String username);


    /**
     * 查询所有用户
     * 选取 it_user 表的所有字段以及 it_role 表的 role_name 字段。
     * 借助 JOIN 操作把 it_user 表和 it_role 表通过 role_id 字段关联起来,
     * order by user_id desc 按照id进行倒序排序
     */
    @SelectProvider(type = UserSqlProvider.class, method = "selectAll")
    @Results({
            @Result(property = "role_id", column = "role_id"),
            @Result(property = "tags", column = "tags"),
            @Result(property = "created_at", column = "created_at"),
            @Result(property = "updated_at", column = "updated_at"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "role.roleId", column = "role_id"),
            @Result(property = "role.roleName", column = "role_name"),
            @Result(property = "role.description", column = "description"),
            @Result(property = "profile_photo_path", column = "profile_photo_path"),
            @Result(property = "user_id", column = "user_id")
    })
    List<User> selectAll(String name, String rolename);
    class UserSqlProvider {
        public String selectAll(String name, String rolename) {
            return new SQL() {{
                SELECT("u.*, r.role_name, r.description");
                FROM("it_user u");
                JOIN("it_role r ON u.role_id = r.role_id");  // 联合查询
                boolean hasWhere = false;  // 记录是否已经存在where子句
                if (name != null && !name.isEmpty()) {
                    WHERE("u.username LIKE CONCAT('%', #{name}, '%')"); // like模糊查询
                    hasWhere = true;  // 存在则需要添加and子句
                }
                if (rolename != null && !rolename.isEmpty()) {
                    if (hasWhere) {
                        AND();
                    }
                    WHERE("r.role_name = #{rolename}");
                }
                ORDER_BY("u.user_id DESC");  //倒序排序
            }}.toString();
        }
    }


    /**
     * 根据用户名查询用户
     */
    @Select ( "select * from it_user where username = #{username}" )
    User selectByUsernameUser(String username);

    // 根据角色名查询角色id
    @Select ( "select * from it_role where role_name = #{role_name}" )
    int selectWithByRolename (String role_name);

    // userid更新password
    @Update ( "update it_user set password = #{password} where user_id = #{user_id}" )
    void updatepassword(String password, Long user_id);

    // 邮件的数据库比对
    @Select ( "select email from it_user where email = #{email}" )
    String selectByEmail(String email);

    // userid删除用户
    @Delete ( "delete from it_user where user_id = #{user_id}")
    void deleteUserById(Long user_id);

}
